package com.jsy.common.util;

import com.jsy.common.dto.CourseInfo;
import org.apache.poi.hssf.eventusermodel.examples.XLS2CSVmra;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.Writer;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class POIUtil {

    public static void main(String[] args) {
        try {
            List<CourseInfo> courseInfos = loadScoreInfo("E:\\汇思工作\\新工作文档\\碧桂园总部\\数据迁移\\20220823博意增量数据\\完成\\待合并单元格数据.xlsx");
            System.out.println(courseInfos);
            MergeExcel(courseInfos);
//            XLS2CSVmra csv = new XLS2CSVmra(new POIFSFileSystem(new File("H:\\gz_watsons_v65\\out\\artifacts\\gz_watsons_v65_war_exploded\\temp\\dataCenterSyncScheduler\\report.xls")), new PrintStream("G:\\汇思工作\\工作文档\\碧桂园总部\\数据迁移\\完成文档\\三分屏课件合并单元格.csv","gbk") , 20);
//            csv.process();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取exel文件内容
     * @param xlsPath
     * @return
     * @throws IOException
     */
    public static List<CourseInfo> loadScoreInfo(String xlsPath) throws IOException {
        List temp = new ArrayList();
        FileInputStream fileIn = new FileInputStream(xlsPath);
//根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new XSSFWorkbook(fileIn);
//获取Excel文档中的第一个表单
        Sheet sht0 = wb0.getSheetAt(0);
//对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if(r.getRowNum()<1){
                continue;
            }
//创建实体类
            CourseInfo info=new CourseInfo();
//取出当前行第1个单元格数据，并封装在info实体stuName属性上
            info.setOne(getCellValueByCell(r.getCell(0))+"");
            info.setTwo(getCellValueByCell(r.getCell(1))+"");
            info.setThree(getCellValueByCell(r.getCell(2))+"");
            info.setFour(getCellValueByCell(r.getCell(3))+"");
            info.setFive(getCellValueByCell(r.getCell(4))+"");
            info.setSix(getCellValueByCell(r.getCell(5))+"");
            info.setSeven(getCellValueByCell(r.getCell(6))+"");
            info.setEight(getCellValueByCell(r.getCell(7))+"");
            info.setNine(getCellValueByCell(r.getCell(8))+"");
            info.setTen(getCellValueByCell(r.getCell(9))+"");
            info.setTen1(getCellValueByCell(r.getCell(10))+"");
            info.setTen2(getCellValueByCell(r.getCell(11))+"");
            info.setTen3(getCellValueByCell(r.getCell(12))+"");
            info.setTen4(getCellValueByCell(r.getCell(13))+"");
            info.setTen5(getCellValueByCell(r.getCell(14))+"");
            info.setTen6(getCellValueByCell(r.getCell(15))+"");
            info.setTen7(getCellValueByCell(r.getCell(16))+"");
            info.setTen8(getCellValueByCell(r.getCell(17))+"");

            temp.add(info);
        }
        fileIn.close();
        return temp;
    }

    /**
     * 根据指定条件合并单元格
     * @param infoList
     * @throws IOException
     */
    public static void MergeExcel(List<CourseInfo> infoList) throws IOException {

        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        XSSFSheet sheet = workbook.createSheet("sheet");

        XSSFRow row0 = sheet.createRow(0);
        createCell(style, row0, 0, "0");
        createCell(style, row0, 1, "1");
        createCell(style, row0, 2, "2");
        createCell(style, row0, 3, "3");
        createCell(style, row0, 4, "4");
        createCell(style, row0, 5, "5");
        createCell(style, row0, 6, "6");
        createCell(style, row0, 7, "7");
        createCell(style, row0, 8, "8");
        createCell(style, row0, 9, "9");
        createCell(style, row0, 10, "10");
        createCell(style, row0, 11, "11");
        createCell(style, row0, 12, "12");
        createCell(style, row0, 13, "13");
        createCell(style, row0, 14, "14");
        createCell(style, row0, 15, "15");
        createCell(style, row0, 16, "16");
        createCell(style, row0, 17, "17");
        XSSFRow row=null;
        String id=null;
        int count=0;
        if (infoList!=null&&infoList.size()>0){
            for (int i = 0; i < infoList.size(); i++) {
                CourseInfo courseInfo = infoList.get(i);
                row= sheet.createRow(i+1);
                createCell(style, row, 0, courseInfo.getOne());
                createCell(style, row, 1, courseInfo.getTwo());
                createCell(style, row, 2, courseInfo.getThree());
                createCell(style, row, 3, courseInfo.getFour());
                createCell(style, row, 4, courseInfo.getFive());
                createCell(style, row, 5, courseInfo.getSix());
                createCell(style, row, 6, courseInfo.getSeven());
                createCell(style, row, 7, courseInfo.getEight());
                createCell(style, row, 8, courseInfo.getNine());
                createCell(style, row, 9, courseInfo.getTen());
                createCell(style, row, 10, courseInfo.getTen1());
                createCell(style, row, 11, courseInfo.getTen2());
                createCell(style, row, 12, courseInfo.getTen3());
                createCell(style, row, 13, courseInfo.getTen4());
                createCell(style, row, 14, courseInfo.getTen5());
                createCell(style, row, 15, courseInfo.getTen6());
                createCell(style, row, 16, courseInfo.getTen7());
                createCell(style, row, 17, courseInfo.getTen8());
                //如果满足条件合并单元格
                if (courseInfo.getTen2().equals(id)){
                   count++;
                }else {
                    if (count>=1){
                        for (int j = 0; j <12 ; j++) {
                            CellRangeAddress region = new CellRangeAddress(i-count, i, j, j);
                            sheet.addMergedRegion(region);
                        }
                    }
                    count=0;
                }
                id=courseInfo.getTen2();

            }
        }

        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        // 这里是合并excel中日期的两行为一行
//        CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
//        sheet.addMergedRegion(region);

        File file = new File("E:\\汇思工作\\新工作文档\\碧桂园总部\\数据迁移\\20220823博意增量数据\\完成\\合并后单元格数据.xlsx");
        FileOutputStream fout = new FileOutputStream(file);
        workbook.write(fout);
        fout.close();
    }

    /**
     * 根据指定条件合并单元格
     * @param infoList
     * @throws IOException
     */
    public static void createExcel(List<CourseInfo> infoList) throws IOException {

        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        XSSFSheet sheet = workbook.createSheet("sheet");

        XSSFRow row0 = sheet.createRow(0);
        createCell(style, row0, 0, "0");
        createCell(style, row0, 1, "1");
        createCell(style, row0, 2, "2");
        createCell(style, row0, 3, "3");
        createCell(style, row0, 4, "4");
        createCell(style, row0, 5, "5");
        createCell(style, row0, 6, "6");
        createCell(style, row0, 7, "7");
        createCell(style, row0, 8, "8");
        createCell(style, row0, 9, "9");
        createCell(style, row0, 10, "10");
        createCell(style, row0, 11, "11");
        createCell(style, row0, 12, "12");
        createCell(style, row0, 13, "13");
        createCell(style, row0, 14, "14");
        createCell(style, row0, 15, "15");
        createCell(style, row0, 16, "16");
        createCell(style, row0, 17, "17");
        XSSFRow row=null;
        String id=null;
        int count=0;
        if (infoList!=null&&infoList.size()>0){
            for (int i = 0; i < infoList.size(); i++) {
                CourseInfo courseInfo = infoList.get(i);
                row= sheet.createRow(i+1);
                createCell(style, row, 0, courseInfo.getOne());
                createCell(style, row, 1, courseInfo.getTwo());
                createCell(style, row, 2, courseInfo.getThree());
                createCell(style, row, 3, courseInfo.getFour());
                createCell(style, row, 4, courseInfo.getFive());
                createCell(style, row, 5, courseInfo.getSix());
                createCell(style, row, 6, courseInfo.getSeven());
                createCell(style, row, 7, courseInfo.getEight());
                createCell(style, row, 8, courseInfo.getNine());
                createCell(style, row, 9, courseInfo.getTen());
                createCell(style, row, 10, courseInfo.getTen1());
                createCell(style, row, 11, courseInfo.getTen2());
                createCell(style, row, 12, courseInfo.getTen3());
                createCell(style, row, 13, courseInfo.getTen4());
                createCell(style, row, 14, courseInfo.getTen5());
                createCell(style, row, 15, courseInfo.getTen6());
                createCell(style, row, 16, courseInfo.getTen7());
                createCell(style, row, 17, courseInfo.getTen8());
                //如果满足条件合并单元格
                if (courseInfo.getTen2().equals(id)){
                    count++;
                }else {
                    if (count>1){
                        for (int j = 0; j <12 ; j++) {
                            CellRangeAddress region = new CellRangeAddress(i-count, i, j, j);
                            sheet.addMergedRegion(region);
                        }
                    }
                    count=0;
                }
                id=courseInfo.getTen2();

            }
        }

        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        // 这里是合并excel中日期的两行为一行
//        CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
//        sheet.addMergedRegion(region);

        File file = new File("F:\\汇思工作\\工作文档\\碧桂园总部\\数据迁移\\增量数据补充\\导出文件\\三分屏合并课程资源(新).xlsx");
        FileOutputStream fout = new FileOutputStream(file);
        workbook.write(fout);
        fout.close();
    }

    public static void createCell(XSSFCellStyle style, XSSFRow row0, int i, String s) {
        XSSFCell cell_00 = row0.createCell(i);
        cell_00.setCellStyle(style);
        cell_00.setCellValue(s);
    }

    public String getCellFromExcel(String path, String row, String col) throws Exception {
        return getCellFromExcel(path, 0, Integer.valueOf(row), Integer.valueOf(col));
    }

    public String getRowFromExcel(String path, String row) throws Exception {
        return getRowFromExcel(path, 0, Integer.valueOf(row));
    }

    public String getRowFromExcel(String path, int sheet, int row) throws Exception {
        FileInputStream xlsx = new FileInputStream(path);
        Workbook workbook = new XSSFWorkbook(xlsx);
        Sheet sheet1 = workbook.getSheetAt(sheet);
        Row row1 = sheet1.getRow(row);
        List<String> list = new ArrayList<>();
        int rowNum = row1.getLastCellNum();
        for (int i = 0; i < rowNum; i++) {
            list.add(getCellValueByCell(row1.getCell(i)));
        }
        return list.toString();
    }


    public String getCellFromExcel(String path, int sheet, int row, int col) throws Exception {
        FileInputStream xlsx = new FileInputStream(path);
        Workbook workbook = new XSSFWorkbook(xlsx);
        Sheet sheet1 = workbook.getSheetAt(sheet);
        Row row1 = sheet1.getRow(row);
        String cell = getCellValueByCell(row1.getCell(col));
        return cell;
    }

    //获取单元格各类型值，返回字符串类型
    private static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType = cell.getCellType();

        // 以下是判断数据的类型
        switch (cellType) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                if (0 == cell.getCellType()) {//判断单元格的类型是否则NUMERIC类型
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat(
                                "yyyy-MM-dd HH:mm");
                        cellValue = formater.format(date);
                    } else {
                        cellValue = cell.getNumericCellValue() + "";
                    }
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * excel裝csv
     * @param inputFile
     * @param outputFile
     * @return
     */
    public static int xlsx2Csv(File inputFile, File outputFile) {
        int record=0;
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();
        try {

            FileOutputStream writer = new FileOutputStream(outputFile);
            Writer fos = new BufferedWriter(new OutputStreamWriter(writer, "gbk"));
            // Get the workbook object for XLSX file
            InputStream inputStream=new FileInputStream(inputFile);
            Workbook workbook = WorkbookFactory.create(inputStream);
            // Get first sheet from the workbook
            Sheet sheet;
            Row row;
            Cell cell;
            System.out.println("==表数=="+workbook.getNumberOfSheets());
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheet = workbook.getSheetAt(i);
                record=sheet.getLastRowNum();
                System.out.println("==表行数=="+sheet.getLastRowNum());
                for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                    row = sheet.getRow(j);
                    if (row==null){
                        data.append("\r\n");
                        continue;
                    }
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        cell = row.getCell(k);
                        if (cell==null){
                            data.append("" + ",");
                            continue;
                        }
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                data.append(cell.getBooleanCellValue() + ",");

                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                data.append(cell.getNumericCellValue() + ",");

                                break;
                            case Cell.CELL_TYPE_STRING:
                                data.append(cell.getStringCellValue().replaceAll(",","、") + ",");
                                break;

                            case Cell.CELL_TYPE_BLANK:
                                data.append("" + ",");
                                break;
                            default:
                                data.append(cell + ",");

                        }
                    }
                    data.append("\r\n");
                }
            }

            fos.write(data.toString());
            fos.close();
            inputStream.close();
        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
        return  record;
    }
}
